DB2中使用事件监控器抓取锁超时、死锁信息 |
您所在的位置:网站首页 › db2 查看表锁 › DB2中使用事件监控器抓取锁超时、死锁信息 |
锁事件监控器可以抓取死锁、锁超时事件,包含这些事件发生时应用正在执行的SQL语句。 具体的介绍可以参考下面的官方文档: http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/
我这里只是做个测试,过程与上面链接中基本一致: C:\windows\system32>db2 update db cfg for sample using locktimeout 10 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. C:\windows\system32> db2 update db cfg for sample using mon_lockwait HISTORY mon_deadlock history mon_locktimeout history DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. C:\windows\system32>db2 connect to sample Database Connection Information Database server = DB2/NT64 10.1.5 SQL authorization ID = MIAOQING... Local database alias = SAMPLE C:\windows\system32>db2 "CREATE EVENT MONITOR LOCKEVMON FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE (TABLE LOCKEVMON)" DB20000I The SQL command completed successfully. C:\windows\system32>db2 set event monitor lockevmon state = 1 DB20000I The SQL command completed successfully. //simulate a locktimeout, 模拟出一个锁超时 C:\windows\system32>db2 flush event monitor LOCKEVMON DB20000I The SQL command completed successfully. C:\windows\system32>db2 set event monitor LOCKEVMON state=0 DB20000I The SQL command completed successfully. C:\windows\system32>db2level DB21085I This instance or install (instance name, where applicable: "DB2INST1") uses "64" bits and DB2 code release "SQL10056" with level identifier "0607010E". Informational tokens are "DB2 v10.5.600.232", "s150731", "IP23638", and Fix Pack "6". Product is installed at "C:\db2installpath" with DB2 Copy Name "DB2COPY1". copy "C:\db2installpath\samples\java\jdbc\db2evmonfmt.java" . copy "C:\db2installpath\samples\java\jdbc\DB2EvmonLocking.xsl" . set PATH=C:\db2installpath\java\jdk\bin;%PATH% javac db2evmonfmt.java java db2evmonfmt -d sample -ue LOCKEVMON -ftext > c:\locktimeout1.txt 下面是locktimeout1.txt的内容: SELECT evmon.xmlreport FROM TABLE ( EVMON_FORMAT_UE_TO_XML( 'LOG_TO_FILE',FOR EACH ROW OF ( SELECT * FROM LOCKEVMON ORDER BY EVENT_ID, EVENT_TIMESTAMP, EVENT_TYPE, MEMBER ))) AS evmon ------------------------------------------------------- Event ID : 1 Event Type : LOCKWAIT Event Timestamp : 2016-04-20-08.54.33.461851 Partition of detection : 0 ------------------------------------------------------- Participant No 1 requesting lock ---------------------------------- Lock Name : 0x02001000110000000000000052 Lock wait start time : 2016-04-20-08.54.28.395295 Lock wait end time : 2016-04-20-08.54.38.580195 Lock Type : ROW Lock Specifics : ROWID=17,DATA_PARTITION_ID=0,PAGEID=0 Lock Attributes : 00000000 Lock mode requested : Update Lock mode held : Exclusive Lock Count : 0 Lock Hold Count : 0 Lock rrIID : 0 Lock Status : Waiting Lock release flags : 00000000 Tablespace TID : 2 Tablespace Name : USERSPACE1 Table FID : 16 Table Schema : MIAOQINGSONG Table Name : SALES Attributes Requester Owner --------------------- ------------------------------ ------------------------------ Participant No 1 2 Application Handle 074 062 Application ID *LOCAL.DB2INST1.160420005337 *LOCAL.DB2INST1.160420005247 Application Name db2bp.exe db2bp.exe Authentication ID MIAOQINGSONG MIAOQINGSONG Requesting AgentID 9184 6056 Coordinating AgentID 9184 6056 Agent Status UOW Executing UOW Waiting Application Action No action No action Lock timeout value 10 0 Lock wait value 5000 0 Workload ID 1 1 Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD Service subclass ID 13 13 Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS Current Request Execute Immediate Execute Immediate TEntry state 1 2 TEntry flags1 00000000 00000000 TEntry flags2 00000200 00000200 Lock escalation no no Client userid Client wrkstnname Client applname Client acctng Utility ID Current Activities of Participant No 1 ---------------------------------------- Activity ID : 1 Uow ID : 1 Package Name : SQLC2K26 Package Schema : NULLID Package Version : Package Token : AAAAAfAd Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Actual degree : 1 Eff locktimeout : 10 Stmt first use : 2016-04-20-08.54.28.385018 Stmt last use : 2016-04-20-08.54.28.385018 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 2070174236673 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt no : 1 Stmt text : delete from sales where sales = 7 Past Activities of Participant No 1 ------------------------------------- Activities not available Current Activities of Participant No 2 ---------------------------------------- Activities not available Past Activities of Participant No 2 ------------------------------------- Past Activities wrapped: no Activity ID : 1 Uow ID : 3 Package Name : SQLC2K26 Package Schema : NULLID Package Version : Package Token : AAAAAfAd Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Actual degree : 1 Eff locktimeout : 10 Stmt first use : 2016-04-20-08.54.15.690439 Stmt last use : 2016-04-20-08.54.15.690439 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 768799145985 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt no : 1 Stmt text : delete from sales where sales=7 ------------------------------------------------------- Event ID : 2 Event Type : LOCKTIMEOUT Event Timestamp : 2016-04-20-08.54.38.580233 Partition of detection : 0 ------------------------------------------------------- Participant No 1 requesting lock ---------------------------------- Lock Name : 0x02001000110000000000000052 Lock wait start time : 2016-04-20-08.54.28.395295 Lock wait end time : 2016-04-20-08.54.38.580233 Lock Type : ROW Lock Specifics : ROWID=17,DATA_PARTITION_ID=0,PAGEID=0 Lock Attributes : 00000000 Lock mode requested : Update Lock mode held : Exclusive Lock Count : 0 Lock Hold Count : 0 Lock rrIID : 0 Lock Status : Waiting Lock release flags : 00000000 Tablespace TID : 2 Tablespace Name : USERSPACE1 Table FID : 16 Table Schema : MIAOQINGSONG Table Name : SALES Attributes Requester Owner --------------------- ------------------------------ ------------------------------ Participant No 1 2 Application Handle 074 062 Application ID *LOCAL.DB2INST1.160420005337 *LOCAL.DB2INST1.160420005247 Application Name db2bp.exe db2bp.exe Authentication ID MIAOQINGSONG MIAOQINGSONG Requesting AgentID 9184 6056 Coordinating AgentID 9184 6056 Agent Status UOW Executing UOW Waiting Application Action No action No action Lock timeout value 10 0 Lock wait value 5000 0 Workload ID 1 1 Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD Service subclass ID 13 13 Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS Current Request Execute Immediate Execute Immediate TEntry state 1 2 TEntry flags1 00000000 00000000 TEntry flags2 00000200 00000200 Lock escalation no no Client userid Client wrkstnname Client applname Client acctng Utility ID Current Activities of Participant No 1 ---------------------------------------- Activity ID : 1 Uow ID : 1 Package Name : SQLC2K26 Package Schema : NULLID Package Version : Package Token : AAAAAfAd Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Actual degree : 1 Eff locktimeout : 10 Stmt first use : 2016-04-20-08.54.28.385018 Stmt last use : 2016-04-20-08.54.28.385018 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 2070174236673 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt no : 1 Stmt text : delete from sales where sales = 7 Past Activities of Participant No 1 ------------------------------------- Activities not available Current Activities of Participant No 2 ---------------------------------------- Activities not available Past Activities of Participant No 2 ------------------------------------- Past Activities wrapped: no Activity ID : 1 Uow ID : 3 Package Name : SQLC2K26 Package Schema : NULLID Package Version : Package Token : AAAAAfAd Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Actual degree : 1 Eff locktimeout : 10 Stmt first use : 2016-04-20-08.54.15.690439 Stmt last use : 2016-04-20-08.54.15.690439 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 768799145985 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt no : 1 Stmt text : delete from sales where sales=7 C:\windows\system32>db2 drop event monitor LOCKEVMON DB20000I The SQL command completed successfully. C:\windows\system32>db2 drop table LOCKEVMON DB20000I The SQL command completed successfully.
注意:如果是AIX或者LINUX环境下,可以在sqllib目录下找到db2evmonfmt.java 和 DB2EvmonLocking.xsl 文件 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |